package com.service.impl;

import com.entity.DictionaryEntry;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.util.FileCopyUtils;
import com.repository.DictionaryRepository;
import com.service.DictionaryService;

import java.io.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

@Service
public class DictionaryServiceImpl implements DictionaryService {

    private final DictionaryRepository dictionaryRepository;
    private final ResourceLoader resourceLoader;

    @Value("${excel.local-path}")
    private String localExcelPath;

    public DictionaryServiceImpl(DictionaryRepository dictionaryRepository, ResourceLoader resourceLoader) {
        this.dictionaryRepository = dictionaryRepository;
        this.resourceLoader = resourceLoader;
    }

  //  @Scheduled(cron = "${scheduler.cron-expression}")
    @Override
    public void updateDictionaryFromExcel() throws IOException {
        try {
            // 优先从本地读取 Excel 文件
            byte[] excelFile = readLocalExcel();
            if (excelFile == null) {
                // 如果本地读取失败，可以考虑添加日志或通知机制，并可以选择是否继续尝试从线上读取
                System.out.println("本地 Excel 文件读取失败，将不进行此次更新。");
                return; // 或者你可以选择继续尝试从线上读取
            }
            // 解析 Excel 文件并更新字典表
            List<DictionaryEntry> entries = parseExcel(excelFile);
            updateDictionaryTable(entries);
            System.out.println("字典表已成功从本地文件更新，共插入 " + entries.size() + " 条记录");
        } catch (IOException e) {
            e.printStackTrace();
            System.err.println("任务执行失败，错误信息：" + e.getMessage());
        }
    }

    @Override
    public byte[] readLocalExcel() throws IOException {
        // 从本地文件系统读取 Excel 文件
        Resource resource = resourceLoader.getResource(localExcelPath);
        if (resource.exists()) {
            try (InputStream inputStream = resource.getInputStream()) {
                // 使用 FileCopyUtils 将 InputStream 转换为 byte 数组
                return FileCopyUtils.copyToByteArray(inputStream);
            }
        } else {
            throw new IOException("本地 Excel 文件不存在");
        }
    }

    @Override
    public List<DictionaryEntry> parseExcel(byte[] file) throws IOException {
        List<DictionaryEntry> entries = new ArrayList<>();
        try (InputStream inputStream = new ByteArrayInputStream(file);
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rowCount; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    String dbName = getStringCellValue(row.getCell(0));
                    String tableName = getStringCellValue(row.getCell(1));
                    String fieldName = getStringCellValue(row.getCell(2));
                    String indexName = getStringCellValue(row.getCell(3));

                    DictionaryEntry entry = new DictionaryEntry();
                    entry.setDbName(dbName);
                    entry.setTableName(tableName);
                    entry.setFieldName(fieldName);
                    entry.setIndexName(indexName);
                    entries.add(entry);
                }
            }
        }
        return entries;
    }

    private String getStringCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }

    @Override
    public void updateDictionaryTable(List<DictionaryEntry> entries) {
        // 清除现有的字典表数据（可选）
        dictionaryRepository.deleteAll();

        // 批量插入新的字典条目
        dictionaryRepository.saveAll(entries);

        System.out.println("字典表已成功更新，共插入 " + entries.size() + " 条记录");
    }


}